package cc.zy.base.businesses.utils;

import cc.zy.base.businesses.entity.ErrorLog;
import cc.zy.base.businesses.entity.NetworkSchool;
import cc.zy.base.common.entity.FebsResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @author wangpin
 * @date 2021/02/01
 * 读取Excel文件数据
 *
 */
@Slf4j
public class ReadAccountExcel {
    public static String REMARK="233网校账号密码导入";
    public static FebsResponse readData(MultipartFile multipartFile) throws NoSuchFieldException, IllegalAccessException, IOException {
        //将表中数据读到此集合中
        List<NetworkSchool> networkSchools = new ArrayList<>();
        //记录错误日志
        List<ErrorLog> errorLogs = new ArrayList<>();
        //将表头信息和对应列数放入map集合中
        Map<String, Object> titleMap = new HashMap<>();
        InputStream inputStream = multipartFile.getInputStream();
        log.debug(multipartFile.getName());
        //获取一个UUID作为当次操作日志记录的唯一标记
        String unique = UUID.randomUUID().toString();

        log.debug(String.format("unique = %s", unique));
        // 工作表
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i1 = 0; i1 < numberOfSheets; i1++) {

            Sheet sheet = workbook.getSheetAt(i1);
            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }
            // 读数据。
            //4、循环读取表格数据
            for (Row row : sheet) {
                int columNos = row.getLastCellNum();// 表头总共的列数
                //目前读取的行数
                int rowNum = row.getRowNum();
                //首行（即表头）不读取
                if (rowNum == 0) {
                    for (int i = 0; i <= columNos; i++) {
                        Cell cell = row.getCell(i);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            titleMap.put(cell.getStringCellValue(),i);
                        }
                    }
                    continue;
                }
                for (Cell cell : row) {
                    cell.setCellType(CellType.STRING);
                }
                NetworkSchool networkSchool = new NetworkSchool();
                //读取当前行中单元格数据，索引从0开始

                /**
                 * 获取模板中的学生基本信息
                 */
                Integer usernameIndex=(Integer) titleMap.get("账号");
                log.info("账号 "+usernameIndex);
                String username=null;
                Cell usernameCell = row.getCell(usernameIndex);
                if (usernameCell == null || usernameCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(usernameIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setUnique(unique);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("账号信息为空！");
                    errorLogs.add(errorLog);
                }else {
                    username = usernameCell.getStringCellValue();
                    networkSchool.setUsername(username);
                }


                Integer passwordIndex=(Integer) titleMap.get("密码");
                Cell passwordCell = row.getCell(passwordIndex);
                String password=null;
                if (passwordCell==null||passwordCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(passwordIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setUnique(unique);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("密码信息为空！");
                    errorLogs.add(errorLog);
                }else {
                    password = passwordCell.getStringCellValue();
                    networkSchool.setPassword(password);
                }

                Integer statusIndex=(Integer) titleMap.get("状态（已激活/未激活）");
                Cell termNameCell = row.getCell(statusIndex);
                String status=null;
                if (termNameCell==null||termNameCell.equals("")){
                    networkSchool.setStatus(status);
                }else {
                    status = termNameCell.getStringCellValue();
                    if (!status.equals("已激活") && !status.equals("未激活")){
                        ErrorLog errorLog = new ErrorLog();
                        errorLog.setErrorRow(rowNum+1);
                        errorLog.setErrorColumn(statusIndex+1);
                        errorLog.setRemark(REMARK);
                        errorLog.setUnique(unique);
                        errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                        errorLog.setReason("状态信息格式错误！（例：已激活/未激活）");
                        errorLogs.add(errorLog);
                    }else {
                        networkSchool.setStatus(status);
                    }
                }

                //设置行号
                networkSchool.setRow(rowNum+1);
                networkSchools.add(networkSchool);
            }
            //5、关闭流
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //判断是否有错误
        if (errorLogs==null||errorLogs.isEmpty()){
            return new FebsResponse().message("200").data(networkSchools);
        }else {
            return new FebsResponse().message("400").data(errorLogs);
        }
    }
}